BigQuery で INFORMATION_SCHEMA から CREATE TABLE 文が取得できるようになりました!

BigQuery で INFORMATION_SCHEMA から CREATE TABLE 文が取得できるようになりました!

Clock Icon2021.04.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

ついにきました! INFORMATION_SCHEMATABLES ビューから、既存テーブルの CREATE TABLE 文が取得できるようになりました!

何がうれしいの?

システムで使用しているデータベースのテーブル定義は CREATE TABLE 構文で SQL ファイルに記載して、コード管理するのが一般的ではないかと思います。

BigQuery の既存テーブルのテーブル定義は、これまでも JSON 形式では取得することができましたが、CREATE TABLE 構文の SQL として欲しい場合、手動で作成する必要がありました。

「テーブルにデータ入れるなら、どうせ初めに手動でテーブル作成しないといけないんじゃないの?」と思われるかもしれんせんが、BigQuery では CSV などのファイルからフォーマットを自動検出してデータをロードする時に自動でテーブルを作成することも可能です。

自動作成ではテーブルやカラムの論理名の付与やパーティショニングなどはしてくれないので、実際にシステムで利用するテーブルは作成しなおす必要があると思いますが、カラムの物理名やデータ型が定義済みのベースとなるテーブル定義が SQL で取得できれば、テーブルを作成&管理するのがだいぶ楽になるのです。

ということで、2021/04/07 現在まだプレビューですが、INFORMATION_SCHEMA から DDL が取得できるようになったこの BigQuery のアップデート、待ってました!v

JSON 形式のテーブル定義を取得

論理名やパーティショニング、クラスタリングや有効期限などの設定された、以下のテーブルのスキーマ情報を取得してみます。

JSON 形式のテーブルスキーマの情報は、bq show --schema コマンドで取得することができます。

Cloud Shell から以下のコマンドで、テーブルスキーマを JSON ファイルに出力しました。

bq show --schema --format=prettyjson dataset_1.table_A1_cp > schema_table_A1_cp.json

出力した JSON ファイルの中身を見てみます。

[
  {
    "description": "STRING\u578b\u30ab\u30e9\u30e0",
    "mode": "REQUIRED",
    "name": "col_string",
    "type": "STRING"
  },
  {
    "description": "INTEGER\u578b\u30ab\u30e9\u30e0",
    "mode": "REQUIRED",
    "name": "col_integer",
    "type": "INTEGER"
  },
  {
    "description": "DATE\u578b\u30ab\u30e9\u30e0",
    "name": "col_date",
    "type": "DATE"
  },
  {
    "description": "DATETIME\u578b\u30ab\u30e9\u30e0",
    "name": "col_datetime",
    "type": "DATETIME"
  },
  {
    "description": "TIMESTAMP\u578b\u30ab\u30e9\u30e0",
    "name": "col_timestamp",
    "type": "TIMESTAMP"
  },
  {
    "description": "RECORD\u578b\u30ab\u30e9\u30e0",
    "fields": [
      {
        "description": "\u30ec\u30b3\u30fc\u30c9A",
        "mode": "REPEATED",
        "name": "a",
        "type": "STRING"
      },
      {
        "description": "\u30ec\u30b3\u30fc\u30c9B",
        "name": "b",
        "type": "BOOLEAN"
      }
    ],
    "name": "col_record",
    "type": "RECORD"
  }
]

カラムの物理名やデータ型、NOT NULL 制約の情報は取得できましたが、論理名はユニコードになっているので、 標準 SQL のテーブル定義に慣れていると、可読性は良くありません。。

また、パーティショニングやクラスタリング、有効期限などその他のテーブル情報は、別途 bq show コマンドでテーブル名を指定して確認する必要があります。

mikami_yuki@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq show dataset_1.table_A1_cp
Table cm-da-mikami-yuki-258308:dataset_1.table_A1_cp

   Last modified                  Schema                 Total Rows   Total Bytes     Expiration                        Time Partitioning                         Clustered Fields           Labels      
 ----------------- ------------------------------------ ------------ ------------- ----------------- -------------------------------------------------------- ------------------------- ---------------- 
  07 Apr 09:26:16   |- col_string: string (required)     0            0             31 Dec 14:59:59   MONTH (field: col_timestamp, expirationMs: 7776000000)   col_string, col_integer   type:partition  
                    |- col_integer: integer (required)                                                                                                                                   sample:table_a  
                    |- col_date: date                                                                                                                                                                    
                    |- col_datetime: datetime                                                                                                                                                            
                    |- col_timestamp: timestamp                                                                                                                                                          
                    +- col_record: record                                                                                                                                                                
                    |  |- a: string (repeated)                                                                                                                                                           
                    |  |- b: boolean                                                                                                                                                                     

やはり、テーブル定義をコード管理する場合、このままのテーブル情報だと扱いにくそうです。。。

INFORMATION_SCHEMA から SQL のテーブル定義を取得

同じテーブルのテーブル定義を、INFORMATION_SCHEMA から取得してみます!

※2021/04/07 現在、日本語の INFORMATION_SCHEMA.TABLES ビューのドキュメントには、まだ DDL カラムの記載はありませんでした。

BigQuery 管理コンソールのクエリエディタから、以下の SQL を実行しました。

SELECT
  table_name, ddl
FROM
  `cm-da-mikami-yuki-258308`.dataset_1.INFORMATION_SCHEMA.TABLES
 WHERE
  table_name = 'table_A1_cp'
;

取得した DDL は以下です。

CREATE TABLE `cm-da-mikami-yuki-258308.dataset_1.table_A1_cp`
(
  col_string STRING NOT NULL OPTIONS(description="STRING型カラム"),
  col_integer INT64 NOT NULL OPTIONS(description="INTEGER型カラム"),
  col_date DATE OPTIONS(description="DATE型カラム"),
  col_datetime DATETIME OPTIONS(description="DATETIME型カラム"),
  col_timestamp TIMESTAMP OPTIONS(description="TIMESTAMP型カラム"),
  col_record STRUCT<a ARRAY<STRING> OPTIONS(description="レコードA"), b BOOL OPTIONS(description="レコードB")> OPTIONS(description="RECORD型カラム")
)
PARTITION BY TIMESTAMP_TRUNC(col_timestamp, MONTH)
CLUSTER BY col_string, col_integer
OPTIONS(
  partition_expiration_days=90.0,
  expiration_timestamp=TIMESTAMP "2021-12-31T14:59:59.000Z",
  description="table_Aから作成",
  labels=[("sample", "table_a"), ("type", "partition")]
);

カラムの物理名や論理名、データ型や NOT NULL 定義はもちろん、パーティショニングやクラスタリング、データの有効期限やラベル情報など、SQL でテーブル定義を取得することができました!

これならこのままコードとして管理できますし、既存テーブルの一部を変更して新しいテーブルを作成する場合や、CREATE TABLE 構文でパーティショニング定義する場合どう書くんだっけ? な時にも参照できるので便利です!

なお、カラム名を指定せず SELECT * で参照すると、ddl カラム値は取得できませんでした。

また、GCS に配置したファイルなどの外部データを参照する外部テーブルの DDL は、やはり取得できませんでした。

INFORMATION_SCHEMATABLES では、テーブルだけでなくビューの情報も参照できます。 確認したところ、ビューの場合にも TABLES.DDL カラムで CREATE VIEW 文が参照できました。

これでテーブルやビューの管理がずいぶん楽になりますv

まとめ(所感)

既存テーブルの DDL を取得できるようになった今回のアップデート、きましたねv

標準 SQL に対応しているとはいえ、一部利用できない SQL 構文もあり、bq コマンドと併用しないと若干使いにくい感が否めなかった BigQuery ですが、 ここのところ ALTER 構文などの標準 SQL 関連のアップデートも続いており、ユーザーとしてはうれしい限りです!

今後もさらなるアップデートに期待してます!

参考

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.